# 快速修复WVP数据库问题
Write-Host "=== WVP数据库快速修复工具 ===" -ForegroundColor Green

# 检查MySQL连接
Write-Host "检查MySQL连接..." -ForegroundColor Yellow
try {
    $connection = New-Object System.Data.Odbc.OdbcConnection
    $connection.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=127.0.0.1;PORT=3306;DATABASE=mysql;UID=root;PWD=12345678;"
    $connection.Open()
    Write-Host "✓ MySQL连接成功" -ForegroundColor Green
    $connection.Close()
} catch {
    Write-Host "❌ MySQL连接失败，尝试其他方法..." -ForegroundColor Red
}

# 创建SQL脚本内容
$sqlContent = @"
-- WVP数据库快速初始化
CREATE DATABASE IF NOT EXISTS wvp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wvp;

-- 创建用户角色表
CREATE TABLE IF NOT EXISTS wvp_user_role (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    authority VARCHAR(255),
    create_time VARCHAR(50),
    update_time VARCHAR(50)
);

-- 创建用户表
CREATE TABLE IF NOT EXISTS wvp_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role_id INT DEFAULT 1,
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    push_key VARCHAR(50)
);

-- 创建媒体服务器表
CREATE TABLE IF NOT EXISTS wvp_media_server (
    id VARCHAR(255) PRIMARY KEY,
    ip VARCHAR(50) NOT NULL,
    hook_ip VARCHAR(50),
    sdp_ip VARCHAR(50),
    stream_ip VARCHAR(50),
    http_port INT,
    http_ssl_port INT,
    rtmp_port INT,
    rtmp_ssl_port INT,
    rtp_proxy_port INT,
    rtsp_port INT,
    rtsp_ssl_port INT,
    auto_config TINYINT(1) DEFAULT 1,
    secret VARCHAR(50),
    rtp_enable TINYINT(1) DEFAULT 0,
    rtp_port_range VARCHAR(50),
    record_assist_port INT,
    default_server TINYINT(1) DEFAULT 0,
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    last_keepalive_time VARCHAR(50),
    status TINYINT(1) DEFAULT 0,
    send_rtp_port_range VARCHAR(50),
    record_day INT DEFAULT 7,
    server_id VARCHAR(50)
);

-- 创建平台表
CREATE TABLE IF NOT EXISTS wvp_platform (
    id INT AUTO_INCREMENT PRIMARY KEY,
    enable TINYINT(1) DEFAULT 0,
    name VARCHAR(255),
    server_gb_id VARCHAR(50),
    server_gb_domain VARCHAR(50),
    server_ip VARCHAR(50),
    server_port INT,
    device_gb_id VARCHAR(50),
    device_ip VARCHAR(50),
    device_port VARCHAR(50),
    username VARCHAR(255),
    password VARCHAR(255),
    expires VARCHAR(50),
    keep_timeout VARCHAR(50),
    transport VARCHAR(50),
    character_set VARCHAR(50),
    catalog_id VARCHAR(50),
    ptz TINYINT(1) DEFAULT 0,
    rtcp TINYINT(1) DEFAULT 0,
    status TINYINT(1) DEFAULT 0,
    start_offline_push TINYINT(1) DEFAULT 0,
    catalog_group INT DEFAULT 1,
    administrative_division VARCHAR(50),
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    as_message_channel TINYINT(1) DEFAULT 0,
    server_id VARCHAR(50)
);

-- 创建设备表
CREATE TABLE IF NOT EXISTS wvp_device (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255),
    manufacturer VARCHAR(255),
    model VARCHAR(255),
    firmware VARCHAR(255),
    transport VARCHAR(50),
    stream_mode VARCHAR(50),
    on_line TINYINT(1) DEFAULT 0,
    register_time VARCHAR(50),
    keepalive_time VARCHAR(50),
    ip VARCHAR(50),
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    port INT,
    expires INT,
    host_address VARCHAR(50),
    charset VARCHAR(50),
    media_server_id VARCHAR(50) DEFAULT 'auto',
    password VARCHAR(255),
    server_id VARCHAR(50)
);

-- 创建设备通道表
CREATE TABLE IF NOT EXISTS wvp_device_channel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    channel_id VARCHAR(50) NOT NULL,
    device_id VARCHAR(50) NOT NULL,
    name VARCHAR(255),
    manufacture VARCHAR(255),
    model VARCHAR(255),
    owner VARCHAR(255),
    civil_code VARCHAR(255),
    block VARCHAR(255),
    address VARCHAR(255),
    parent_id VARCHAR(50),
    safety_way INT,
    register_way INT,
    cert_num VARCHAR(255),
    certifiable INT,
    err_code INT,
    end_time VARCHAR(255),
    secrecy VARCHAR(255),
    ip_address VARCHAR(255),
    port INT,
    password VARCHAR(255),
    ptz_type INT,
    status TINYINT(1) DEFAULT 0,
    longitude DOUBLE,
    latitude DOUBLE,
    stream_id VARCHAR(255),
    device_type VARCHAR(50),
    channel_type INT,
    create_time VARCHAR(50),
    update_time VARCHAR(50),
    sub_count INT DEFAULT 0,
    parental INT DEFAULT 0,
    has_audio TINYINT(1) DEFAULT 0,
    record_plan_id INT
);

-- 创建录像计划表
CREATE TABLE IF NOT EXISTS wvp_record_plan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    snap TINYINT(1) DEFAULT 0,
    name VARCHAR(255) NOT NULL,
    create_time VARCHAR(50),
    update_time VARCHAR(50)
);

-- 创建录像计划项表
CREATE TABLE IF NOT EXISTS wvp_record_plan_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start INT,
    stop INT,
    week_day INT,
    plan_id INT,
    create_time VARCHAR(50),
    update_time VARCHAR(50)
);

-- 插入默认数据
INSERT IGNORE INTO wvp_user_role VALUES (1, 'admin', '0', '2021-04-13 14:14:57', '2021-04-13 14:14:57');
INSERT IGNORE INTO wvp_user VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 1, '2021-04-13 14:14:57', '2021-04-13 14:14:57', '3e80d1762a324d5b0ff636e0bd16f1e3');
"@

# 保存SQL到文件
$sqlFile = "wvp_quick_fix.sql"
$sqlContent | Out-File -FilePath $sqlFile -Encoding UTF8
Write-Host "✓ SQL脚本已生成: $sqlFile" -ForegroundColor Green

Write-Host "`n=== 解决方案 ===" -ForegroundColor Cyan
Write-Host "请选择以下任一方法执行SQL脚本：" -ForegroundColor White

Write-Host "`n方法1: 使用MySQL命令行工具" -ForegroundColor Yellow
Write-Host "mysql -h 127.0.0.1 -P 3306 -u root -p12345678 < $sqlFile" -ForegroundColor White

Write-Host "`n方法2: 使用图形化工具（推荐）" -ForegroundColor Yellow
Write-Host "1. 打开 phpMyAdmin、Navicat、MySQL Workbench 等工具" -ForegroundColor White
Write-Host "2. 连接到 MySQL (127.0.0.1:3306, root/12345678)" -ForegroundColor White
Write-Host "3. 执行 $sqlFile 文件中的SQL语句" -ForegroundColor White

Write-Host "`n方法3: 在线MySQL工具" -ForegroundColor Yellow
Write-Host "1. 访问 https://www.db4free.net/ 或类似在线工具" -ForegroundColor White
Write-Host "2. 连接到本地MySQL" -ForegroundColor White
Write-Host "3. 执行SQL脚本" -ForegroundColor White

Write-Host "`n执行完成后，重新启动WVP应用程序即可！" -ForegroundColor Green
Write-Host "访问地址: http://localhost:18080" -ForegroundColor Green
Write-Host "登录信息: admin / admin" -ForegroundColor Green

Read-Host "`n按回车键退出"
